﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace TPAMain数据维护工具V2
{
    public partial class frmQJFixer : Form
    {
        SqlTools sqt;

        /// <summary>
        /// 默认查询语句
        /// </summary>
        string strDefualtSelectString_ck = "select hta021 订单类型,hta011 合同号,htb001 订单编号,htb002 订单项次,htb003 型号规格,htb006 订货数量,htb018 订单出库数量,isnull(jhb013,0) 计划出库数量,isnull(a.出库合计,0)-isnull(b.出库退回合计,0) 实际出库数量 from hta_table, scjhb_table, htc_table, htb_table left join (select gxck010, gxck011, sum(gxck015) 出库合计 from gxck_table where(gxck016= 'Y' or gxck016 = 'K') and gxck053 = 1 group by gxck010,gxck011) as a on a.gxck010=htb001 and a.gxck011=htb002 left join (select gxck010, gxck011, sum(thb010) 出库退回合计 from cktha_table ,ckthb_table ,gxck_table where(gxck016= 'Y' or gxck016 = 'K') and gxck053 = 1 and tha007 = 'Y' and tha008 = '关联订单计划完工数量' and tha001 = thb001 and thb003 = gxck001 group by gxck010,gxck011) as b on b.gxck010 =htb001 and b.gxck011 =htb002 where hta014='Y' and htc001 = hta001 and hta001 = htb001 and hta026 = '603车间' and htb001 = jhb003 and htb002 = jhb004 and hta011 not like '%宏科%' and hta001 <>'201703100065' and hta001 <>'201706090043' and hta001 <>'201706160002' and  hta021<>'二筛' and htc002 not like '%合同为二筛合同%' and htc002 not like '%二次筛选补充合同%' and htc002 not like '%合同为筛选合同%' and (htb018<> isnull(出库合计,0)-isnull(出库退回合计,0) or jhb013<>isnull(出库合计,0)-isnull(出库退回合计,0))";
        string strDefualtSelectString_xh = "select hta011 合同号,htb001 订单编号,htb002 订单项次,htb003 型号规格,htb006 订货数量,htb011 订单销货数量,isnull(a.销货合计,0)-ISNULL(b.销退数量,0) 实际销货数量 from hta_table,htc_table ,htb_table left join (select gxck010, gxck011, sum(xhb008) 销货合计 from gxck_table,orxha_table ,orxhb_table where xha013='Y' and xhb032 = '603' and xha001 = xhb001 and LEFT(xhb014,12)=gxck001 group by gxck010, gxck011) as a on a.gxck010=htb001 and a.gxck011=htb002 left join (select gxck010 订单编号, gxck011 订单项次, SUM(xtb011) 销退数量 from orxta_table ,orxtb_table,orxhb_table ,gxck_table where xta001=xtb001 and xtb003=xhb001 and xtb004=xhb002 and LEFT(xhb014,12)=gxck001 and xta009='Y' and xta017 = '补货退货' and xhb032 = '603' group by gxck010,gxck011) as b on b.订单编号=htb001 and b.订单项次=htb002 where hta001=htc001 and hta014='Y' and hta001 = htb001 and hta026 = '603车间' and isnull(a.销货合计,0)-ISNULL(b.销退数量,0)<>htb011 and hta001 <>'201703100065' and hta001 <>'201706090043' and hta001 <>'201706160002' and hta021<>'二筛' and htc002 not like '%合同为二筛合同%' and htc002 not like '%二次筛选补充合同%' and htc002 not like '%合同为筛选合同%'";
        /// <summary>
        /// 根据订单编号和订单项次查询对应的生产计划
        /// </summary>
        string strSelectScjh = "select jhb001 计划单号,jhb002 计划项次,jhb005 型号规格,jhb008 计划数量,jhb013 出库数量,jha014 生产车间 from scjha_table ,scjhb_table where jha007='Y' and jha001=jhb001 and jhb003='@订单编号' and jhb004='@订单项次'";

        /// <summary>
        /// 根据订单编号和订单项次查询对应的成品出库
        /// </summary>
        string strSelectGxck = "select gxck001 出库单号,gxck012 型号规格,gxck007 工单号,gxck017 生产批号,gxck015 出库数量,gxck019 出库退回数量,gxck016 单据状态 from gxck_table where gxck010='@订单编号' and gxck011='@订单项次' and (gxck016='Y' or gxck016='K') and gxck053 = 1";

        /// <summary>
        /// 根据订单编号和订单项次查询对应的成品出库退回
        /// </summary>
        string strSelectCkth = "select tha008 退货类别,thb003 出库单号,thb005 工单号,thb006 生产批号,thb010 退回数量 from cktha_table ,ckthb_table where tha007='Y' and tha001=thb001 and thb003 in (select gxck001 from gxck_table where gxck010='@订单编号' and gxck011='@订单项次' and (gxck016='Y' or gxck016='K') and gxck053 = 1)";

        /// <summary>
        /// 根据订单编号和订单项次查询对应的销货单
        /// </summary>
        string strSelectXh = "select xhb001 销货单号,xhb002 销货项次,xhb003 型号规格,xhb008 销货数量 from orxha_table ,orxhb_table where xha013='Y' and xha001=xhb001 and xhb032='603' and LEFT(xhb014,12) in (select gxck001 from gxck_table where gxck010='@订单编号' and gxck011='@订单项次' and (gxck016='Y' or gxck016='K'))";

        /// <summary>
        /// 更新订单指定字段值
        /// </summary>
        string strUpdateHtb = "update htb_table set @字段='@值' where htb001='@订单编号' and htb002='@订单项次'";

        /// <summary>
        /// 更新生产计划出库数量
        /// </summary>
        string strUpdateScjh = "update scjhb_table set jhb013=htb018 from htb_table where jhb003=htb001 and jhb004=htb002 and htb001='@订单编号'";
        string strUpdateScjh2 = "update scjhb_table set jhb013=htb018 from htb_table where jhb003=htb001 and jhb004=htb002 and htb001='@订单编号' and htb002='@订单项次'";

        /// <summary>
        /// 去除二筛合同欠交
        /// </summary>
        string strUpdateHtbQj_es = "update htb_table set htb018=htb006 from hta_table,htc_table where hta014='Y' and hta001=htb001 and htc001=hta001 and htc002 like '@备注'";
        string strUpdateHtbQj2_es = "update htb_table set htb018=htb006 from hta_table where hta014='Y' and hta001=htb001 and hta021 like '%二筛%'";

        /// <summary>
        /// 去除二筛生产计划欠交
        /// </summary>
        string strUpdateJhQj_es = "update scjhb_table set jhb013=jhb008 from hta_table,htb_table,htc_table where hta014='Y' and hta001=htb001 and htc001=hta001 and jhb003=htb001 and jhb004=htb002 and htc002 like '@备注'";
        string strUpdateJhQj2_es = "update scjhb_table set jhb013=jhb008 from hta_table,htb_table where hta014='Y' and hta001=htb001 and hta021 like '%二筛%'  and jhb003=htb001 and jhb004=htb002";

        public frmQJFixer()
        {
            InitializeComponent();
        }

        private void frmQJFixer_Load(object sender, EventArgs e)
        {
            sqt = new SqlTools(GeneralTools.GetConnectionString(Properties.Settings.Default.Host, Properties.Settings.Default.DBName));
        }

        private void tp1_chkOrderDate_CheckedChanged(object sender, EventArgs e)
        {
            tp1_dtpFrom.Enabled = tp1_chkOrderDate.Checked;
            tp1_dtpTo.Enabled = tp1_chkOrderDate.Checked;
        }

        private void tp1_chkOrderID_CheckedChanged(object sender, EventArgs e)
        {
            tp1_txtOrderID.Enabled = tp1_chkOrderID.Checked;
        }

        private void tp1_btnCheck_Click(object sender, EventArgs e)
        {
            string strTmpSelectString_ck = strDefualtSelectString_ck;
            string strTmpSelectString_xh = strDefualtSelectString_xh;
            if (tp1_chkOrderDate.Checked)
            {
                strTmpSelectString_ck += " and hta002 between '" + tp1_dtpFrom.Value.ToString("yyyyMMdd") + "' and '" + tp1_dtpTo.Value.ToString("yyyyMMdd") + "'";
                strTmpSelectString_xh += " and hta002 between '" + tp1_dtpFrom.Value.ToString("yyyyMMdd") + "' and '" + tp1_dtpTo.Value.ToString("yyyyMMdd") + "'";
            }
            if (tp1_chkOrderID.Checked)
            {
                strTmpSelectString_ck += " and (hta001 like '" + tp1_txtOrderID.Text.Trim() + "' or hta011 like '" + tp1_txtOrderID.Text.Trim() + "')";
                strTmpSelectString_xh += " and (hta001 like '" + tp1_txtOrderID.Text.Trim() + "' or hta011 like '" + tp1_txtOrderID.Text.Trim() + "')";
            }
            strTmpSelectString_ck += " order by htb001,htb002";
            strTmpSelectString_xh += " order by htb001,htb002";
            DataTable tmpdt = sqt.GetData(strTmpSelectString_ck);
            tp1_tp5_dgv1.DataSource = tmpdt;
            DataTable tmpdt2 = sqt.GetData(strTmpSelectString_xh);
            tp1_tp6_dgv1.DataSource = tmpdt2;
            tabControl3.SelectedIndex = 0;
        }

        private void dgv_CurrentCellChanged(object sender, EventArgs e)
        {
            DataGridView tmpdgv = tp1_tp5_dgv1;
            switch (tabControl3.SelectedIndex)
            {
                case 0:
                    tmpdgv = tp1_tp5_dgv1;
                    break;
                case 1:
                    tmpdgv = tp1_tp6_dgv1;
                    break;
            }
            if (tp1_chkShowDetail.Checked && tmpdgv.CurrentRow != null)
            {
                string strOrderID = GeneralTools.GetString(tmpdgv.CurrentRow.Cells["订单编号"].Value, string.Empty);
                string strOrderIdx = GeneralTools.GetString(tmpdgv.CurrentRow.Cells["订单项次"].Value, string.Empty);
                tp1_tp1_dgv1.DataSource = sqt.GetData(strSelectScjh.Replace("@订单编号", strOrderID)
                                                                                                            .Replace("@订单项次", strOrderIdx));
                tp1_tp2_dgv1.DataSource = sqt.GetData(strSelectGxck.Replace("@订单编号", strOrderID)
                                                                                                            .Replace("@订单项次", strOrderIdx));
                tp1_tp3_dgv1.DataSource = sqt.GetData(strSelectCkth.Replace("@订单编号", strOrderID)
                                                                                                            .Replace("@订单项次", strOrderIdx));
                tp1_tp4_dgv1.DataSource = sqt.GetData(strSelectXh.Replace("@订单编号", strOrderID)
                                                                                                            .Replace("@订单项次", strOrderIdx));
                tp1_tp1_dgv1.AutoResizeColumns();
                tp1_tp2_dgv1.AutoResizeColumns();
                tp1_tp3_dgv1.AutoResizeColumns();
                tp1_tp4_dgv1.AutoResizeColumns();
            }
            else
            {
                tp1_tp1_dgv1.DataSource = null;
                tp1_tp2_dgv1.DataSource = null;
                tp1_tp3_dgv1.DataSource = null;
                tp1_tp4_dgv1.DataSource = null;
            }
        }

        private void tp1_chkShowDetail_CheckedChanged(object sender, EventArgs e)
        {
            dgv_CurrentCellChanged(this, new EventArgs());
        }

        private void tabControl2_SelectedIndexChanged(object sender, EventArgs e)
        {
            switch (tabControl2.SelectedIndex)
            {
                case 0:
                    tp1_tp1_dgv1.AutoResizeColumns();
                    break;
                case 1:
                    tp1_tp2_dgv1.AutoResizeColumns();
                    break;
                case 2:
                    tp1_tp3_dgv1.AutoResizeColumns();
                    break;
                case 3:
                    tp1_tp4_dgv1.AutoResizeColumns();
                    break;
            }
        }

        private void tp1_btnFixSelected_Click(object sender, EventArgs e)
        {
            switch (tabControl3.SelectedIndex)
            {
                case 0:
                    if (tp1_tp5_dgv1.SelectedRows.Count > 0)
                    {
                        tp1_pb1.Visible = true;
                        tp1_lProgress.Visible = true;
                        tp1_pb1.Value = 0;
                        tp1_pb1.Maximum = tp1_tp5_dgv1.SelectedRows.Count;
                        for (int i = 0; i < tp1_tp5_dgv1.SelectedRows.Count; i++)
                        {
                            tp1_lProgress.Text = GeneralTools.GetString(i + 1, string.Empty) + "/" + tp1_tp5_dgv1.SelectedRows.Count.ToString();
                            tp1_pb1.Value = i + 1;
                            Application.DoEvents();
                            string strOrderID = GeneralTools.GetString(tp1_tp5_dgv1.SelectedRows[i].Cells["订单编号"].Value, string.Empty);
                            string strOrderIdx = GeneralTools.GetString(tp1_tp5_dgv1.SelectedRows[i].Cells["订单项次"].Value, string.Empty);
                            int iDDCK = GeneralTools.GetInt(tp1_tp5_dgv1.SelectedRows[i].Cells["订单出库数量"].Value, 0);
                            int iJHCK = GeneralTools.GetInt(tp1_tp5_dgv1.SelectedRows[i].Cells["计划出库数量"].Value, 0);
                            int iSJCK = GeneralTools.GetInt(tp1_tp5_dgv1.SelectedRows[i].Cells["实际出库数量"].Value, 0);
                            if (iDDCK != iSJCK)
                            {
                                sqt.DoCmdExecuteNonQuery(strUpdateHtb.Replace("@字段", "htb018")
                                                                                                        .Replace("@值", iSJCK.ToString())
                                                                                                        .Replace("@订单编号", strOrderID)
                                                                                                        .Replace("@订单项次", strOrderIdx));
                                SQLLogHelper.WriteLog("订单" + strOrderID + "的第" + strOrderIdx + "项成品出库数量由" + iDDCK.ToString() + "修改为" + iSJCK.ToString());
                                sqt.DoCmdExecuteNonQuery(strUpdateScjh.Replace("@订单编号", strOrderID));
                            }
                            if (iDDCK != iJHCK)
                            {
                                sqt.DoCmdExecuteNonQuery(strUpdateScjh2.Replace("@订单编号", strOrderID)
                                                                                                        .Replace("@订单项次", strOrderIdx));
                                SQLLogHelper.WriteLog("订单" + strOrderID + "的第" + strOrderIdx + "项生产计划出库数量由" + iJHCK.ToString() + "修改为" + iDDCK.ToString());
                            }
                        }
                        tp1_pb1.Visible = false;
                        tp1_lProgress.Visible = false;
                        GeneralTools.ShowInfoMsg(tp1_tp5_dgv1.SelectedRows.Count.ToString() + "项已修改！");
                        tp1_btnCheck_Click(this, new EventArgs());
                    }
                    break;
                case 1:
                    if (tp1_tp6_dgv1.SelectedRows.Count > 0)
                    {
                        tp1_pb1.Visible = true;
                        tp1_lProgress.Visible = true;
                        tp1_pb1.Value = 0;
                        tp1_pb1.Maximum = tp1_tp6_dgv1.SelectedRows.Count;
                        for (int i = 0; i < tp1_tp6_dgv1.SelectedRows.Count; i++)
                        {
                            tp1_lProgress.Text = GeneralTools.GetString(i + 1, string.Empty) + "/" + tp1_tp6_dgv1.SelectedRows.Count.ToString();
                            tp1_pb1.Value = i + 1;
                            Application.DoEvents();
                            string strOrderID = GeneralTools.GetString(tp1_tp6_dgv1.SelectedRows[i].Cells["订单编号"].Value, string.Empty);
                            string strOrderIdx = GeneralTools.GetString(tp1_tp6_dgv1.SelectedRows[i].Cells["订单项次"].Value, string.Empty);
                            int iDDXH = GeneralTools.GetInt(tp1_tp6_dgv1.SelectedRows[i].Cells["订单销货数量"].Value, 0);
                            int iSJXH = GeneralTools.GetInt(tp1_tp6_dgv1.SelectedRows[i].Cells["实际销货数量"].Value, 0);
                            if (iDDXH != iSJXH)
                            {
                                sqt.DoCmdExecuteNonQuery(strUpdateHtb.Replace("@字段", "htb011")
                                                                                                        .Replace("@值", iSJXH.ToString())
                                                                                                        .Replace("@订单编号", strOrderID)
                                                                                                        .Replace("@订单项次", strOrderIdx));
                                SQLLogHelper.WriteLog("订单" + strOrderID + "的第" + strOrderIdx + "项销货数量由" + iDDXH.ToString() + "修改为" + iSJXH.ToString());
                            }
                        }
                        tp1_pb1.Visible = false;
                        tp1_lProgress.Visible = false;
                        GeneralTools.ShowInfoMsg(tp1_tp6_dgv1.SelectedRows.Count.ToString() + "项已修改！");
                        tp1_btnCheck_Click(this, new EventArgs());
                    }
                    break;
            }
        }

        private void tabControl3_SelectedIndexChanged(object sender, EventArgs e)
        {
            switch (tabControl3.SelectedIndex)
            {
                case 0:
                    tp1_tp5_dgv1.AutoResizeColumns();
                    break;
                case 1:
                    tp1_tp6_dgv1.AutoResizeColumns();
                    break;
            }
        }

        private void tp1_btnSelectAll_Click(object sender, EventArgs e)
        {
            DataGridView tmpdgv = tp1_tp5_dgv1;
            switch (tabControl3.SelectedIndex)
            {
                case 0:
                    tmpdgv = tp1_tp5_dgv1;
                    break;
                case 1:
                    tmpdgv = tp1_tp6_dgv1;
                    break;
            }
            if (tmpdgv.Rows.Count > 0)
            {
                for (int i = 0; i < tmpdgv.Rows.Count; i++)
                {
                    tmpdgv.Rows[i].Selected = true;
                }
            }
        }

        private void tp1_btnUnselect_Click(object sender, EventArgs e)
        {
            DataGridView tmpdgv = tp1_tp5_dgv1;
            switch (tabControl3.SelectedIndex)
            {
                case 0:
                    tmpdgv = tp1_tp5_dgv1;
                    break;
                case 1:
                    tmpdgv = tp1_tp6_dgv1;
                    break;
            }
            if (tmpdgv.Rows.Count > 0)
            {
                for (int i = 0; i < tmpdgv.Rows.Count; i++)
                {
                    tmpdgv.Rows[i].Selected = !tmpdgv.Rows[i].Selected;
                }
            }
        }

        private void tp2_btnFix_Es_Click(object sender, EventArgs e)
        {
            if (tp2_lstEsKeywords.Items.Count > 0)
            {
                for (int i = 0; i < tp2_lstEsKeywords.Items.Count; i++)
                {
                    sqt.DoCmdExecuteNonQuery(strUpdateHtbQj_es.Replace("@备注", GeneralTools.GetString(tp2_lstEsKeywords.Items[i], string.Empty)));
                    sqt.DoCmdExecuteNonQuery(strUpdateJhQj_es.Replace("@备注", GeneralTools.GetString(tp2_lstEsKeywords.Items[i], string.Empty)));
                    //清二筛订单
                    sqt.DoCmdExecuteNonQuery(strUpdateHtbQj2_es);
                    sqt.DoCmdExecuteNonQuery(strUpdateJhQj2_es);
                }
                GeneralTools.ShowInfoMsg("更新完成！");
            }
        }

        private void tp1_timer_CheckedChanged(object sender, EventArgs e)
        {
            if (!timer1.Enabled )
            {
                timer1.Interval = ((int)nudTimer.Value) * 60 * 1000;
            }
            timer1.Enabled = tp1_timer.Checked;
        }

        private void timer1_Tick(object sender, EventArgs e)
        {
            //获取数据源，自动检查仅限当年订单
            string strTmpSelectString_ck = strDefualtSelectString_ck;
            string strTmpSelectString_xh = strDefualtSelectString_xh;
            strTmpSelectString_ck += " and hta002 between '" + DateTime.Now.Year.ToString() + "0101' and '" + DateTime.Now.Year.ToString() + "1231'";
            strTmpSelectString_xh += " and hta002 between '" + DateTime.Now.Year.ToString() + "0101' and '" + DateTime.Now.Year.ToString() + "1231'";
            DataTable tmpdt = sqt.GetData(strTmpSelectString_ck);
            DataTable tmpdt2 = sqt.GetData(strTmpSelectString_xh);
            //自动修复
            if(tmpdt .Rows.Count >0)
            {
                for (int i = 0; i < tmpdt.Rows.Count; i++)
                {
                    string strOrderID = GeneralTools.GetString(tmpdt.Rows[i]["订单编号"], string.Empty);
                    string strOrderIdx = GeneralTools.GetString(tmpdt.Rows[i]["订单项次"], string.Empty);
                    int iDDCK = GeneralTools.GetInt(tmpdt.Rows[i]["订单出库数量"], 0);
                    int iJHCK = GeneralTools.GetInt(tmpdt.Rows[i]["计划出库数量"], 0);
                    int iSJCK = GeneralTools.GetInt(tmpdt.Rows[i]["实际出库数量"], 0);
                    if (iDDCK != iSJCK)
                    {
                        sqt.DoCmdExecuteNonQuery(strUpdateHtb.Replace("@字段", "htb018")
                                                                                                .Replace("@值", iSJCK.ToString())
                                                                                                .Replace("@订单编号", strOrderID)
                                                                                                .Replace("@订单项次", strOrderIdx));
                        SQLLogHelper.WriteLog("订单" + strOrderID + "的第" + strOrderIdx + "项成品出库数量由" + iDDCK.ToString() + "自动修改为" + iSJCK.ToString());
                        sqt.DoCmdExecuteNonQuery(strUpdateScjh.Replace("@订单编号", strOrderID));
                    }
                    if (iDDCK != iJHCK)
                    {
                        sqt.DoCmdExecuteNonQuery(strUpdateScjh2.Replace("@订单编号", strOrderID)
                                                                                                .Replace("@订单项次", strOrderIdx));
                        SQLLogHelper.WriteLog("订单" + strOrderID + "的第" + strOrderIdx + "项生产计划出库数量由" + iJHCK.ToString() + "自动修改为" + iDDCK.ToString());
                    }
                }
            }
            if (tmpdt.Rows.Count > 0)
            {
                for (int i = 0; i < tp1_tp6_dgv1.SelectedRows.Count; i++)
                {
                    string strOrderID = GeneralTools.GetString(tmpdt2.Rows[i]["订单编号"], string.Empty);
                    string strOrderIdx = GeneralTools.GetString(tmpdt2.Rows[i]["订单项次"], string.Empty);
                    int iDDXH = GeneralTools.GetInt(tmpdt2.Rows[i]["订单销货数量"], 0);
                    int iSJXH = GeneralTools.GetInt(tmpdt2.Rows[i]["实际销货数量"], 0);
                    if (iDDXH != iSJXH)
                    {
                        sqt.DoCmdExecuteNonQuery(strUpdateHtb.Replace("@字段", "htb011")
                                                                                                .Replace("@值", iSJXH.ToString())
                                                                                                .Replace("@订单编号", strOrderID)
                                                                                                .Replace("@订单项次", strOrderIdx));
                        SQLLogHelper.WriteLog("订单" + strOrderID + "的第" + strOrderIdx + "项销货数量由" + iDDXH.ToString() + "修改为" + iSJXH.ToString());
                    }
                }
            }
        }
    }
}
